package com.xdjmall.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.stream.Stream;

import com.xdjmall.dao.GoodsDao;
import com.xdjmall.entity.Goods;
import com.xdjmall.service.dto.StockNumDTO;
import com.xdjmall.util.DBUtils;
import com.xdjmall.util.QueryCondition;
import com.xdjmall.util.SQLHandler;

public class GoodsDaoImpl implements GoodsDao {
	@Override
	public Integer insert(Goods t) throws SQLException {
		// TODO 张惟诚
		String sql = "insert into goods(goods_name,goods_intro,goods_detail_content,tag) values(?,?,?,?)";
		try (Connection c = DBUtils.getConnection(); 
			PreparedStatement ps = c.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);) {
			ps.setString(1, t.getGoodsName());
			ps.setString(2, t.getGoodsIntro());
			ps.setString(3, t.getGoodsDetailContent());
			ps.setString(4, t.getTag());
			int row = ps.executeUpdate();
			ResultSet keySet = ps.getGeneratedKeys();
			if (keySet.next()) {
				t.setGoodsId(keySet.getInt(1));
			}
			return row;
		}
	}

	@Override
	public Integer delete(Integer id) throws SQLException {
		// 不实现
		return null;
	}

	@Override
	public Integer update(Goods t) throws SQLException {
		// TODO 张惟诚
		String sql = "update goods set ";
		if (t.getGoodsName() != null) {
			sql += " goods_name=?,";
		}
		if (t.getGoodsIntro() != null) {
			sql += " goods_intro=?,";
		}
		if (t.getGoodsId() != null) {
			sql += " category_id=?,";
		}
		if (t.getGoodsCoverImg() != null) {
			sql += " goods_cover_img=?,";
		}
		if (t.getGoodsCarousel() != null) {
			sql += " goods_carousel=?,";
		}
		if (t.getGoodsDetailContent() != null) {
			sql += " goods_detail_content=?,";
		}
		if (t.getOriginalPrice() != null) {
			sql += " original_price=?,";
		}
		if (t.getSellingPrice() != null) {
			sql += " selling_price=?,";
		}
		if (t.getStockNum() != null) {
			sql += " stock_num=?,";
		}
		if (t.getTag() != null) {
			sql += " tag=?,";
		}
		if (t.getGoodsSellStatus() != null) {
			sql += " goods_sell_status=?,";
		}
		if (t.getCreateUser() != null) {
			sql += " create_user=?,";
		}
		if (t.getCreateTime() != null) {
			sql += " create_time=?,";
		}
		if (t.getUpdateUser() != null) {
			sql += " update_user=?,";
		}
		if (t.getUpdateTime() != null) {
			sql += " update_time=?,";
		}
		// 截取并删除逗号 substring 包前不包后
		sql = sql.substring(0, sql.length() - 1);
		sql += " where goods_id=?";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			int num = 1;
			if (t.getGoodsName() != null) {
				ps.setString(num++, t.getGoodsName());
			}
			if (t.getGoodsIntro() != null) {
				ps.setString(num++, t.getGoodsIntro());
			}
			if (t.getGoodsId() != null) {
				ps.setInt(num++, t.getCategoryId());
			}
			if (t.getGoodsCoverImg() != null) {
				ps.setString(num++, t.getGoodsCoverImg());
			}
			if (t.getGoodsCarousel() != null) {
				ps.setString(num++, t.getGoodsCarousel());
			}
			if (t.getGoodsDetailContent() != null) {
				ps.setString(num++, t.getGoodsDetailContent());
			}
			if (t.getOriginalPrice() != null) {
				ps.setBigDecimal(num++, t.getOriginalPrice());
			}
			if (t.getSellingPrice() != null) {
				ps.setBigDecimal(num++, t.getSellingPrice());
			}
			if (t.getStockNum() != null) {
				ps.setInt(num++, t.getStockNum());
			}
			if (t.getTag() != null) {
				ps.setString(num++, t.getTag());
			}
			if (t.getGoodsSellStatus() != null) {
				ps.setInt(num++, t.getGoodsSellStatus());
			}
			if (t.getCreateUser() != null) {
				ps.setObject(num++, t.getCreateUser());
			}
			if (t.getCreateTime() != null) {
				ps.setObject(num++, t.getCreateTime());
			}
			if (t.getUpdateUser() != null) {
				ps.setInt(num++, t.getUpdateUser());
			}
			if (t.getUpdateTime() != null) {
				ps.setObject(num++, t.getUpdateTime());
			}
			ps.setInt(num, t.getGoodsId());
			return ps.executeUpdate();
		}
	}

	@Override
	public Goods selectById(Integer id) throws SQLException {
		// TODO 张惟诚
		String sql = "select goods_id,goods_name,goods_intro,category_id,goods_cover_img,goods_carousel,goods_detail_content,original_price,selling_price,stock_num,tag,goods_sell_status,create_user,create_time,update_user,update_time from goods where goods_id=?";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				Goods g = new Goods();
				g.setGoodsId(rs.getInt(1));
				g.setGoodsName(rs.getString(2));
				g.setGoodsIntro(rs.getString(3));
				g.setCategoryId(rs.getInt(4));
				g.setGoodsCoverImg(rs.getString(5));
				g.setGoodsCarousel(rs.getString(6));
				g.setGoodsDetailContent(rs.getString(7));
				g.setOriginalPrice(rs.getBigDecimal(8));
				g.setSellingPrice(rs.getBigDecimal(9));
				g.setStockNum(rs.getInt(10));
				g.setTag(rs.getString(11));
				g.setGoodsSellStatus(rs.getInt(12));
				g.setCreateUser(rs.getInt(13));
				g.setCreateTime(rs.getDate(14).toLocalDate());
				g.setUpdateUser(rs.getInt(15));
				g.setUpdateTime(rs.getDate(16).toLocalDate());
				return g;
			}
		}
		return null;
	}

	@Override
	public Integer selectCount() throws SQLException {
		// TODO 张惟诚
		String sql = "select count(*) from goods";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		}
		return null;
	}

	@Override
	public List<Goods> selectByPage(QueryCondition condition) throws SQLException {
		// TODO 送温暖
		List<Goods> list = new ArrayList<>();
		String sql = "select * from goods ";

		Integer page = condition.getPage();
		Integer pageSize = condition.getPageSize(); // 分页条件
		if (page != null && pageSize != null) {
			sql += " limit ?,? ";
		}
		int num = 1;
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {

			if (page != null && pageSize != null) {
				ps.setInt(num, (page - 1) * pageSize);
				num++;
				ps.setInt(num, pageSize);
			}
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Goods g = new Goods();
				g.setGoodsId(rs.getInt(1));
				g.setGoodsName(rs.getString(2));
				g.setGoodsIntro(rs.getString(3));
				g.setCategoryId(rs.getInt(4));
				g.setGoodsCoverImg(rs.getString(5));
				g.setGoodsCarousel(rs.getString(6));
				g.setGoodsDetailContent(rs.getString(7));
				g.setOriginalPrice(rs.getBigDecimal(8));
				g.setSellingPrice(rs.getBigDecimal(9));
				g.setStockNum(rs.getInt(10));
				g.setTag(rs.getString(11));
				g.setGoodsSellStatus(rs.getInt(12));
				g.setCreateUser(rs.getInt(13));
				g.setCreateTime(rs.getDate(14).toLocalDate());
				g.setUpdateUser(rs.getInt(15));
				g.setUpdateTime(rs.getDate(16).toLocalDate());
				list.add(g);
			}
		}
		return list.isEmpty() ? Collections.emptyList() : list;
	}

	@Override
	public Integer getTotalGoods(QueryCondition condition) throws SQLException {
		// TODO 送温暖
		String sql = "select count(*) from goods where 1=1 ";
		String goodsName = (String) condition.get("goodsName");
		Integer goodsSellStatus = (Integer) condition.get("goodsSellStatus");
		LocalDate startTime = (LocalDate) condition.get("startTime");
		LocalDate endTime = (LocalDate) condition.get("endTime");
		if (goodsName != null) {
			sql += " and instr(goods_name,?)>0 ";
		}
		if (goodsSellStatus != null) {
			sql += " and goods_sell_status= ?";
		}
		if (startTime != null) {
			sql += " and create_time > ?";
		}
		if (endTime != null) {
			sql += " and create_time < ?";
		}

		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			int num = 1;
			if (goodsName != null) {
				ps.setString(num++, goodsName);
			}
			if (goodsSellStatus != null) {
				ps.setInt(num++, goodsSellStatus);
			}
			if (startTime != null) {
				ps.setDate(num++, Date.valueOf(startTime));
			}
			if (endTime != null) {
				ps.setDate(num++, Date.valueOf(endTime));
			}
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
			return null;
		}
	}

	@Override
	public List<Goods> selectByIds(List<Integer> goodsIds) throws SQLException {
		// TODO 送温暖
		List<Goods> list = new ArrayList<>();
		String sql = "select * from goods where goods_id " + SQLHandler.joinIn(goodsIds) + " order by goods_id ";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Goods g = new Goods();
				g.setGoodsId(rs.getInt(1));
				g.setGoodsName(rs.getString(2));
				g.setGoodsIntro(rs.getString(3));
				g.setCategoryId(rs.getInt(4));
				g.setGoodsCoverImg(rs.getString(5));
				g.setGoodsCarousel(rs.getString(6));
				g.setGoodsDetailContent(rs.getString(7));
				g.setOriginalPrice(rs.getBigDecimal(8));
				g.setSellingPrice(rs.getBigDecimal(9));
				g.setStockNum(rs.getInt(10));
				g.setTag(rs.getString(11));
				g.setGoodsSellStatus(rs.getInt(12));
				g.setCreateUser(rs.getInt(13));
				g.setCreateTime(rs.getDate(14).toLocalDate());
				g.setUpdateUser(rs.getInt(15));
				g.setUpdateTime(rs.getDate(16).toLocalDate());
				list.add(g);
			}
		}
		return list.isEmpty() ? Collections.emptyList() : list;
	}

	@Override
	public List<Goods> selectGoodsListBySearch(QueryCondition condition) throws SQLException {
		// TODO 送温暖
		List<Goods> list = new ArrayList<>();
		Integer page = condition.getPage();
		Integer pageSize = condition.getPageSize(); // 分页条件
		String sql = "select * from goods where 1=1 ";
		// 如果条件中包含商品名，根据商品模糊查询
		String keyword = (String) condition.get("keyword");
		Integer categoryId = (Integer) condition.get("categoryId");
		Integer goodsSellStatus = (Integer) condition.get("goodsSellStatus");
		String orderBy = (String) condition.get("orderBy");
		if (keyword != null) {
			sql += " and instr(goods_name,?) > 0 ";
		}
		if (categoryId != null) {
			sql += " and category_id = ?";
		}
		if (goodsSellStatus != null) {
			sql += " and goods_sell_status = ?";
		}
		if (orderBy != null) {
			switch (orderBy) {
			case "new":
				sql += " order by goods_id desc";
				break;
			case "price":
				sql += " order by selling_price asc";
				break;
			default:
				sql += " order by stock_num desc";
				break;
			}
		}
		if (page != null && pageSize != null) {
			sql += " limit ?,?";
		}

		int num = 1;
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			if (keyword != null) {
				ps.setString(num++, keyword);
			}
			if (categoryId != null) {
				ps.setInt(num++, categoryId);
			}
			if (goodsSellStatus != null) {
				ps.setInt(num++, goodsSellStatus);
			}
			if (page != null && pageSize != null) {
				ps.setInt(num++, (page - 1) * pageSize);
				ps.setInt(num++, pageSize);
			}

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Goods g = new Goods();
				g.setGoodsId(rs.getInt(1));
				g.setGoodsName(rs.getString(2));
				g.setGoodsIntro(rs.getString(3));
				g.setCategoryId(rs.getInt(4));
				g.setGoodsCoverImg(rs.getString(5));
				g.setGoodsCarousel(rs.getString(6));
				g.setGoodsDetailContent(rs.getString(7));
				g.setOriginalPrice(rs.getBigDecimal(8));
				g.setSellingPrice(rs.getBigDecimal(9));
				g.setStockNum(rs.getInt(10));
				g.setTag(rs.getString(11));
				g.setGoodsSellStatus(rs.getInt(12));
				g.setCreateUser(rs.getInt(13));
				g.setCreateTime(rs.getDate(14).toLocalDate());
				g.setUpdateUser(rs.getInt(15));
				g.setUpdateTime(rs.getDate(16).toLocalDate());
				list.add(g);
			}
		}

		return list.isEmpty() ? Collections.emptyList() : list;
	}

	@Override
	public int getTotalGoodsBySearch(QueryCondition condition) throws SQLException {
		// TODO 刘青云
		return selectGoodsListBySearch(condition).size();
	}

	@Override
	public Integer batchInsert(List<Goods> goodsList) throws SQLException {
		String sql = "insert into goods(goods_name,goods_intro,goods_detail_content,tag) values(?,?,?,?)";
		int row = 0;
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			for (int i = 0; i < goodsList.size(); i++) {
				ps.setString(1, goodsList.get(i).getGoodsName());
				ps.setString(2, goodsList.get(i).getGoodsIntro());

				ps.setString(3, goodsList.get(i).getGoodsDetailContent());

				ps.setString(4, goodsList.get(i).getTag());

				ps.addBatch();
			}
			int[] rows = ps.executeBatch();
			return rows.length;
		}
	}

	@Override
	public Integer updateStockNum(List<StockNumDTO> snds) throws SQLException {
		// TODO 刘青云
		String sql = "update goods set stock_num=stock_num-? "
				+ " where goods_id = ? and stock_num >= ? and goods_sell_status = 0";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			// 遍历
			for (StockNumDTO snd : snds) {
				ps.setInt(1, snd.getGoodsCount());
				ps.setInt(2, snd.getGoodsId());
				ps.setInt(3, snd.getGoodsCount());
				// 添加到批处理
				ps.addBatch();
			}
			int[] executeBatch = ps.executeBatch();
			return 1;
		}
	}

	@Override
	public Integer batchUpdateSellStatus(Integer[] goodsIds, Integer sellStatus) throws SQLException {
		// TODO 刘青云
		String sql = "update goods set goods_sell_status = ?,update_time=now() " + " where goods_id "
				+ SQLHandler.joinIn(goodsIds);
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, sellStatus);
			return ps.executeUpdate();
		}
	}
}
